Coffee Chain Sales Analysis

Project: Coffee Chain Sales Analysis

This project demonstrates my ability to use SQL for complex data analysis and derive meaningful business intelligence.

Business Context

Our coffee chain has been expanding rapidly, and management needed a comprehensive understanding of our sales performance across different locations, products, and time periods. The goals of this analysis were to:

Identify sales trends to optimize inventory and staffing
Understand which products and categories drive the most revenue
Evaluate store performance to inform future expansion decisions
Categorize products based on their sales performance for targeted marketing

Data Preparation

ALTER TABLE coffee_brand.coffee_shop_sales ADD COLUMN transaction_day DATE;
UPDATE coffee_brand.coffee_shop_sales 
SET transaction_date = STR_TO_DATE(transaction_date, '%m/%d/%Y');
ALTER TABLE coffee_brand.coffee_shop_sales
MODIFY COLUMN transaction_date DATE;

UPDATE coffee_brand.coffee_shop_sales
SET transaction_time = STR_TO_DATE(transaction_time, '%H:%i:%s');
ALTER TABLE coffee_brand.coffee_shop_sales
MODIFY COLUMN transaction_time TIME;

To ensure accurate time-based analysis, I prepared the data by:

Adding a new column for transaction day
Converting string dates to proper DATE format
Converting string times to proper TIME format

This standardization allows for more precise temporal analysis and easier querying of time-based data.

1. Daily, Weekly, and Monthly Sales Trends

SELECT 
    DATE_FORMAT(transaction_date, '%Y-%m') AS Month, 
    ROUND(SUM(unit_price * transaction_qty),1) AS total_sales
FROM 
    coffee_brand.coffee_shop_sales
GROUP BY 
    DATE_FORMAT(transaction_date, '%Y-%m');

This query analyzes monthly sales trends by:

Grouping transactions by month
Calculating total sales for each month

This information helps identify seasonal patterns and overall sales performance over time.

2. Average Transaction Value by Store Location

SELECT ROUND(AVG(transaction_qty*unit_price),2) as avg_of_a_transaction, store_location
 FROM  coffee_brand.coffee_shop_sales
GROUP BY store_location;

This analysis calculates the average transaction value for each store location, helping to:

Identify high-performing locations
Understand customer spending patterns across different stores

3. Top Contributing Product Categories and Types

SELECT product_category, product_type, ROUND(SUM(transaction_qty* unit_price),2) AS total_sales
FROM   coffee_brand.coffee_shop_sales
GROUP BY product_category, product_type
ORDER BY  product_type ASC
LIMIT 10;

This query identifies the top-selling product categories and types by:

Calculating total sales for each product category and type
Ordering results to show top performers

This information is crucial for inventory management and marketing strategies.

4. Revenue and Quantity Sold by Product Category

SELECT ROUND(SUM(transaction_qty*unit_price),2) AS revenue,
       SUM(transaction_qty) AS quantity_sold, product_category
 FROM  coffee_brand.coffee_shop_sales
 GROUP BY product_category;

This analysis provides insights into each product category's performance by:

Calculating total revenue and quantity sold for each category

This helps in understanding which categories are driving sales and which might need attention.

5. Product Sales Status

WITH sales_data AS (
    SELECT 
        product_type,
        SUM(transaction_qty) AS total_quantity_sold,
        AVG(SUM(transaction_qty)) OVER () AS avg_quantity_sold
    FROM 
        coffee_brand.coffee_shop_sales
    GROUP BY 
        product_type
)
SELECT 
    product_type,
    total_quantity_sold,
    avg_quantity_sold,
    CASE 
        WHEN total_quantity_sold > avg_quantity_sold THEN 'Above Average'
        WHEN total_quantity_sold < avg_quantity_sold THEN 'Below Average'
        ELSE 'Average'
    END AS product_status
FROM 
    sales_data;

This advanced query categorizes products based on their sales performance:

Calculates total quantity sold for each product type
Compares each product's sales to the overall average
Assigns a status (Above Average, Below Average, or Average) to each product

This analysis helps identify star products and those that might need marketing or quality improvements.